﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
//using MySql.Data.MySqlClient;

namespace Web.Dal
{
    public class C_SqlOperate
    {

        #region 公共属性
        /// <summary>
        /// 返回公用错误信息，并不保证错误信息的原始性
        /// </summary>
        public static string ErrMsg
        {
            get { return aErrMsg; }
        }

        /// <summary>
        /// 返回或设置连接字符串
        /// </summary>
        public static string SqlConnStr
        {
            get { return aDefConnStr; }
            set
            {
                if (value.Trim() == string.Empty)
                    throw new Exception("数据库连接字符不能为空。");
                else
                    aDefConnStr = value;
            }
        }

        /// <summary>
        /// 数据库驱动列表
        /// </summary>
        public static Dictionary<String, String> ProviderNameList
        {
            get { return aProviderNameList; }
        }
        #endregion

        #region 私有属性

        private static String aDefConnConfName = "DatabaseConnString";

        /// <summary>
        /// 默认的数据库连接字符串
        /// </summary>
        private static String aDefConnStr = ConfigurationManager.ConnectionStrings[aDefConnConfName].ConnectionString;

        /// <summary>
        /// 默认的数据库驱动名称
        /// </summary>
        private static String aDefProviderName = ConfigurationManager.ConnectionStrings[aDefConnConfName].ProviderName;

        /// <summary>
        /// 最后一次的错误信息
        /// </summary>
        private static String aErrMsg = "";

        /// <summary>
        /// 验证数据库非零自一开始的数据库ID正则表达式
        /// </summary>
        private static Regex aRegex_SqlID = new Regex(@"^[1-9]{1}[\d]*$");

        /// <summary>
        /// 验证数值型正则表达式
        /// </summary>
        private static Regex aRegex_Num = new Regex(@"^[0-9]*$");

        /// <summary>
        /// 数据库连接字符串列表（包括默认连接字符串）
        /// </summary>
        private static Dictionary<String, String> aConnStrList = new Dictionary<String, String>();

        /// <summary>
        /// 数据库驱动列表（包括默认的数据库驱动）
        /// <para>Key必须与数据库连接字符串一致，如果没有找到相关名称的key，则会使用默认的数据库驱动</para>
        /// </summary>
        private static Dictionary<String, String> aProviderNameList = new Dictionary<String, String>();

        #endregion

        #region 公共方法

        static C_SqlOperate()
        {
            InitConfigList();
        }

        private static void InitConfigList()
        {
            Int32 iListCount = ConfigurationManager.ConnectionStrings.Count;
            aConnStrList.Clear();
            aProviderNameList.Clear();
            for (Int32 i = 0; i < iListCount; i++)
            {
                aConnStrList.Add(ConfigurationManager.ConnectionStrings[i].Name, ConfigurationManager.ConnectionStrings[i].ConnectionString);
                aProviderNameList.Add(ConfigurationManager.ConnectionStrings[i].Name, ConfigurationManager.ConnectionStrings[i].ProviderName);
            }
        }

        /// <summary>
        /// 验证SqlID字符，主要用于数字类型的防注入(注意SqlID不能为0)。
        /// </summary>
        /// <param name="Value">验证的字符串</param>
        /// <returns></returns>
        public static Boolean CheckValueForSqlID(String Value)
        {
            return aRegex_SqlID.IsMatch(Value);
        }

        /// <summary>
        /// 验证数字字符
        /// </summary>
        /// <param name="Value">验证的字符串</param>
        /// <returns></returns>
        public static Boolean CheckValueForNum(String Value)
        {
            return aRegex_Num.IsMatch(Value);
        }

        /// <summary>
        /// 根据默认驱动程序名称和连接字符串生成并打开一个新的连接实例
        /// </summary>
        /// <returns></returns>
        private static DbConnection CreateConnection()
        {
            return CreateConnection(aDefConnConfName);
        }

        /// <summary>
        /// 根据指定驱动程序名称和连接字符串生成并打开一个新的连接实例
        /// </summary>
        /// <param name="Key">配置名称</param>
        /// <returns></returns>
        private static DbConnection CreateConnection(String Key)
        {
            DbConnection iConn = null;
            if (!aConnStrList.ContainsKey(Key))
            {
                aErrMsg = "指定的数据库连接设置错误！";
                return iConn;
            }

            try
            {
                DbProviderFactory iDbFactory = DbProviderFactories.GetFactory(
                    aProviderNameList.ContainsKey(Key) ? aProviderNameList[Key] : aDefProviderName);
                iConn = iDbFactory.CreateConnection();
                iConn.ConnectionString = aConnStrList[Key];
                iConn.Open();
            }
            catch (Exception Err)
            {
                aErrMsg = Err.Message;
                return null;
            }

            return iConn;
        }

        /// <summary>
        /// 根据当前驱动程序名称新建一个新的DataAdapter实例
        /// </summary>
        /// <returns></returns>
        private static DbDataAdapter CreateDataAdapter()
        {
            return CreateDataAdapter(aDefConnConfName);
        }

        /// <summary>
        /// 根据指定的驱动程序名称新建一个新的DataAdapter实例
        /// </summary>
        /// <param name="Key">配置名称</param>
        /// <returns></returns>
        private static DbDataAdapter CreateDataAdapter(String Key)
        {
            DbDataAdapter iRetAdp = null;
            if (!aProviderNameList.ContainsKey(Key))
            {
                aErrMsg = "指定的数据库驱动设置错误！";
                return iRetAdp;
            }
            try
            {
                DbProviderFactory iDbFactory = DbProviderFactories.GetFactory(aProviderNameList[Key]);
                iRetAdp = iDbFactory.CreateDataAdapter();
            }
            catch (Exception Err)
            {
                aErrMsg = Err.Message;
                return null;
            }

            return iRetAdp;
        }

        /// <summary>
        /// 执行SQL语句，并返回第一行第一列数据，其他丢弃
        /// <para>null则错误</para>
        /// </summary>
        /// <param name="SqlStr">SQL语句</param>
        /// <returns></returns>
        public static Object ExecCommStr_O(String SqlStr)
        {
            return ExecCommStr_O(SqlStr, aDefConnConfName);
        }

        /// <summary>
        /// 使用指定数据库配置执行SQL语句，并返回第一行第一列数据，其他丢弃
        /// </summary>
        /// <param name="SqlStr">SQL语句</param>
        /// <param name="Key">配置名称</param>
        /// <returns></returns>
        public static Object ExecCommStr_O(String SqlStr, String Key)
        {
            Object iRetObj = null;
            if (SqlStr.Trim() == String.Empty)
            {
                aErrMsg = "空的SQL语句。";
                return null;
            }
            using (DbConnection iConnObj = CreateConnection(Key))
            {
                if (iConnObj == null)
                {
                    return null;
                }

                DbCommand iDBComm = iConnObj.CreateCommand();
                iDBComm.CommandText = SqlStr;

                try
                {
                    iRetObj = iDBComm.ExecuteScalar();
                }
                catch (Exception Err)
                {
                    aErrMsg = Err.Message;
                    return null;
                }
                finally
                {
                    if (iDBComm != null)
                    {
                        iDBComm.Dispose();
                        iDBComm = null;
                    }
                }
            }

            return iRetObj;
        }

        /// <summary>
        /// 执行SQL语句，并返回影响的行数
        /// <para>-1则错误</para>
        /// </summary>
        /// <param name="SqlStr">SQL语句</param>
        /// <param name="CloseConn">是否关闭连接</param>
        /// <returns></returns>
        public static Int32 ExecCommStr_I(String SqlStr,Boolean CloseConn)
        {
            return ExecCommStr_I(SqlStr, CloseConn, aDefConnConfName);
        }

        /// <summary>
        /// 使用指定的配置执行SQL语句，并返回影响的行数
        /// <para>-1则错误</para>
        /// </summary>
        /// <param name="SqlStr">SQL语句</param>
        /// <param name="CloseConn">是否关闭连接</param>
        /// <param name="Key">配置名称</param>
        /// <returns></returns>
        public static Int32 ExecCommStr_I(String SqlStr, Boolean CloseConn, String Key)
        {
            Int32 iRetInt = -1;
            DbConnection iDbConn = CreateConnection(Key);
            if (iDbConn == null)
            {
                return iRetInt;
            }

            DbCommand iDbComm = iDbConn.CreateCommand();
            iDbComm.CommandText = SqlStr;
            try
            {
                iRetInt = iDbComm.ExecuteNonQuery();
            }
            catch (Exception Err)
            {
                aErrMsg = Err.Message;
                return -1;
            }
            finally
            {
                if (iDbComm != null)
                {
                    iDbComm.Dispose();
                    iDbComm = null;
                }
                if (CloseConn)
                {
                    if (iDbConn != null)
                    {
                        iDbConn.Close();
                        iDbConn.Dispose();
                        iDbConn = null;
                    }
                }
            }

            return iRetInt;
        }

        /// <summary>
        /// 执行SQL语句，并返回DbDataReader对象(注意该方法并未释放连接，如有需要请在使用完DbDataReader后手工释放)
        /// <para>null则出错</para>
        /// </summary>
        /// <param name="SqlStr">SQL语句</param>
        /// <returns></returns>
        public static DbDataReader ExecCommStr_Reader(String SqlStr)
        {
            return ExecCommStr_Reader(SqlStr, aDefConnConfName);
        }

        /// <summary>
        /// 使用指定的配置执行SQL语句，并返回DbDataReader对象(注意该方法并未释放连接，如有需要请在使用完DbDataReader后手工释放)
        /// <para>null则出错</para>
        /// </summary>
        /// <param name="SqlStr">SQL语句</param>
        /// <param name="Key">配置名称</param>
        /// <returns></returns>
        public static DbDataReader ExecCommStr_Reader(String SqlStr, String Key)
        {
            DbDataReader iRetReader = null;
            DbConnection iConnObj = CreateConnection(Key);
            if (iConnObj == null)
            {
                return iRetReader;
            }

            DbCommand iDbComm = iConnObj.CreateCommand();
            iDbComm.CommandText = SqlStr;

            try
            {
                iRetReader = iDbComm.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception Err)
            {
                aErrMsg = Err.Message;
                return null;
            }
            finally
            {
                if (iDbComm != null)
                {
                    iDbComm.Dispose();
                    iDbComm = null;
                }
            }

            return iRetReader;
        }

        /// <summary>
        /// 使用指定的SQL语句，填充数据表
        /// </summary>
        /// <param name="SqlStr">SQL语句</param>
        /// <param name="FillDataTable">数据表</param>
        /// <returns></returns>
        public static Boolean ExecSqlStr_FillTable(String SqlStr, DataTable FillDataTable)
        {
            return ExecSqlStr_FillTable(SqlStr, FillDataTable, aDefConnConfName);
        }

        /// <summary>
        /// 使用指定的数据库配置和SQL语句，填充数据表
        /// </summary>
        /// <param name="SqlStr">SQL语句</param>
        /// <param name="FillDataTable">数据表</param>
        /// <param name="Key">配置名称</param>
        /// <returns></returns>
        public static Boolean ExecSqlStr_FillTable(String SqlStr, DataTable FillDataTable, String Key)
        {
            using (DbConnection iDbConn = CreateConnection(Key))
            {
                if (iDbConn == null)
                {
                    return false;
                }
                DbCommand iDbComm = iDbConn.CreateCommand();
                iDbComm.CommandText = SqlStr;
                using (DbDataAdapter iDbAdp = CreateDataAdapter(Key))
                {
                    if (iDbAdp == null)
                    {
                        return false;
                    }
                    iDbAdp.SelectCommand = iDbComm;
                    try
                    {
                        iDbAdp.Fill(FillDataTable);
                    }
                    catch (Exception Err)
                    {
                        aErrMsg = Err.Message;
                        return false;
                    }
                    finally
                    {
                        if (iDbComm != null)
                        {
                            iDbComm.Dispose();
                            iDbComm = null;
                        }
                    }
                }
            }

            return true;
        }

        /// <summary>
        /// 执行指定配置名称和名称的SQL存储过程
        /// </summary>
        /// <param name="SqlStr">存储过程名称</param>
        /// <param name="ParameterValues">参数的值</param>
        /// <param name="IsHaveOutPutPar">是否有返回参数</param>
        /// <param name="IsDataReader">是否返回DataReader</param>
        /// <param name="IsDataTable">是否返回DataTable</param>
        /// <param name="DbDataTable">返回的DataTable对象</param>
        /// <param name="DbDataReader">返回的DataReader</param>
        /// <param name="OutPutParValue">返回参数的值</param>
        /// <param name="Key">配置名称</param>
        /// <returns></returns>
        public static Boolean ExecProcedure_Sql(String SqlStr, Object[] ParameterValues
            , Boolean IsHaveOutPutPar, Boolean IsDataReader, Boolean IsDataTable
            , ref DataTable DbDataTable, out DbDataReader DbDataReader, String Key)
        {
            DbDataReader = null;
            if (aProviderNameList[Key] == "")
            {
                return false;
            }
            DbConnection iConnObj = CreateConnection(Key);
            if (iConnObj == null)
            {
                return false;
            }

            DbCommand iDbComm = iConnObj.CreateCommand();
            iDbComm.CommandText = SqlStr;
            iDbComm.CommandType = CommandType.StoredProcedure;
            if (GetParametersForCommStr_MsSql(iDbComm as SqlCommand) == false)
            {
                return false;
            }

            if (iDbComm.Parameters.Count != ParameterValues.Length)
            {
                aErrMsg = "所需的参数与输入的参数数量不符！";
                return false;
            }

            try
            {
                Int32 iParCount = iDbComm.Parameters.Count;
                for (Int32 i = 0; i < iParCount; i++)
                {
                    if (ParameterValues[i] == null)
                    {
                        iDbComm.Parameters[i].Value = DBNull.Value;
                    }
                    else
                    {
                        iDbComm.Parameters[i].Value = ParameterValues[i];
                    }
                }

                if (IsDataReader)
                {
                    DbDataReader = iDbComm.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else if (IsDataTable)
                {
                    using (DbDataAdapter iDbap = CreateDataAdapter(Key))
                    {
                        iDbap.SelectCommand = iDbComm;
                        iDbap.Fill(DbDataTable);
                    }
                }
                else
                {
                    iDbComm.ExecuteNonQuery();
                }

                if (IsHaveOutPutPar)
                {
                    for (Int32 o = 0; o < iParCount; o++)
                    {
                        if ((iDbComm.Parameters[o].Direction == ParameterDirection.InputOutput)
                            || (iDbComm.Parameters[o].Direction == ParameterDirection.Output))
                        {
                            ParameterValues[o] = iDbComm.Parameters[o].Value;
                        }
                    }
                }
            }
            catch (Exception Err)
            {
                aErrMsg = Err.Message;
                return false;
            }
            finally
            {
                if (iDbComm != null)
                {
                    iDbComm.Dispose();
                    iDbComm = null;
                }
                if (!IsDataReader)
                {
                    if (iConnObj != null)
                    {
                        iConnObj.Close();
                        iConnObj.Dispose();
                        iConnObj = null;
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 执行指定名称的SQL存储过程
        /// </summary>
        /// <param name="SqlStr">存储过程名称</param>
        /// <param name="ParameterValues">参数的值</param>
        /// <param name="IsHaveOutPutPar">是否有返回参数</param>
        /// <param name="IsDataReader">是否返回DataReader</param>
        /// <param name="IsDataTable">是否返回DataTable</param>
        /// <param name="DbDataTable">返回的DataTable对象</param>
        /// <param name="DbDataReader">返回的DataReader</param>
        /// <param name="OutPutParValue">返回参数的值</param>
        /// <returns></returns>
        public static Boolean ExecProcedure_Sql(String SqlStr, Object[] ParameterValues
            , Boolean IsHaveOutPutPar, Boolean IsDataReader, Boolean IsDataTable
            ,ref DataTable DbDataTable, out DbDataReader DbDataReader)
        {
            return ExecProcedure_Sql(SqlStr, ParameterValues, IsHaveOutPutPar, IsDataReader, IsDataTable, ref DbDataTable, out DbDataReader, aDefConnConfName);
        }

        /// <summary>
        /// 执行指定名称的SQL存储过程
        /// </summary>
        /// <param name="SqlStr">存储过程名称</param>
        /// <param name="ParameterValues">参数的值</param>
        /// <returns></returns>
        public static Boolean ExecProcedure_Sql(String SqlStr, Object[] ParameterValues)
        {
            DbDataReader iDataReader = null;
            DataTable iDataTable = new DataTable();
            return ExecProcedure_Sql(SqlStr, ParameterValues, false, false, false, ref iDataTable, out iDataReader);
        }

        /// <summary>
        /// 执行指定名称的SQL存储过程
        /// </summary>
        /// <param name="SqlStr">存储过程名称</param>
        /// <param name="ParameterValues">参数的值</param>
        /// <param name="DataReader">返回的DataReader对象</param>
        /// <returns></returns>
        public static Boolean ExecProcedure_Sql(String SqlStr, Object[] ParameterValues, Boolean IsHaveOutPutPar)
        {
            DbDataReader iDataReader = null;
            DataTable iDataTable = new DataTable();
            return ExecProcedure_Sql(SqlStr, ParameterValues, IsHaveOutPutPar, false, false, ref iDataTable, out iDataReader);
        }

        /// <summary>
        /// 执行指定名称的SQL存储过程
        /// </summary>
        /// <param name="SqlStr">存储过程名称</param>
        /// <param name="ParameterValues">参数的值</param>
        /// <param name="DataReader">返回的DataReader对象</param>
        /// <param name="Key">配置名称</param>
        /// <returns></returns>
        public static Boolean ExecProcedure_Sql(String SqlStr, Object[] ParameterValues, Boolean IsHaveOutPutPar, String Key)
        {
            DbDataReader iDataReader = null;
            DataTable iDataTable = new DataTable();
            return ExecProcedure_Sql(SqlStr, ParameterValues, IsHaveOutPutPar, false, false, ref iDataTable, out iDataReader, Key);
        }

        /// <summary>
        /// 执行指定名称的SQL存储过程
        /// </summary>
        /// <param name="SqlStr">存储过程名称</param>
        /// <param name="ParameterValues">参数的值</param>
        /// <param name="DataReader">返回的DataReader对象</param>
        /// <returns></returns>
        public static Boolean ExecProcedure_Sql(String SqlStr, Object[] ParameterValues,out DbDataReader DataReader)
        {
            DataReader = null;
            DataTable iDataTable = new DataTable();
            return ExecProcedure_Sql(SqlStr, ParameterValues, false, true, false, ref iDataTable, out DataReader);
        }

        /// <summary>
        /// 执行指定名称的SQL存储过程
        /// </summary>
        /// <param name="SqlStr">存储过程名称</param>
        /// <param name="ParameterValues">参数的值</param>
        /// <param name="DataReader">返回的DataReader对象</param>
        /// <param name="Key">配置名称</param>
        /// <returns></returns>
        public static Boolean ExecProcedure_Sql(String SqlStr, Object[] ParameterValues, out DbDataReader DataReader,String Key)
        {
            DataReader = null;
            DataTable iDataTable = new DataTable();
            return ExecProcedure_Sql(SqlStr, ParameterValues, false, true, false, ref iDataTable, out DataReader, Key);
        }

        /// <summary>
        /// 执行指定名称的SQL存储过程
        /// </summary>
        /// <param name="SqlStr">存储过程名称</param>
        /// <param name="ParameterValues">参数的值</param>
        /// <param name="DataTable">返回的DataTable对象</param>
        /// <returns></returns>
        public static Boolean ExecProcedure_Sql(String SqlStr, Object[] ParameterValues, ref DataTable DataTable)
        {
            DbDataReader iDataReader = null;
            return ExecProcedure_Sql(SqlStr, ParameterValues, false, false, true, ref DataTable, out iDataReader);
        }

        /// <summary>
        /// 执行指定名称的SQL存储过程
        /// </summary>
        /// <param name="SqlStr">存储过程名称</param>
        /// <param name="ParameterValues">参数的值</param>
        /// <param name="DataTable">返回的DataTable对象</param>
        /// <param name="Key">配置名称</param>
        /// <returns></returns>
        public static Boolean ExecProcedure_Sql(String SqlStr, Object[] ParameterValues, ref DataTable DataTable, String Key)
        {
            DbDataReader iDataReader = null;
            return ExecProcedure_Sql(SqlStr, ParameterValues, false, false, true, ref DataTable, out iDataReader, Key);
        }

        /// <summary>
        /// 执行指定名称的SQL存储过程
        /// </summary>
        /// <param name="SqlStr">存储过程名称</param>
        /// <param name="ParameterValues">参数的值</param>
        /// <param name="DataReader">返回的DataReader对象</param>
        /// <param name="OutPutParValue">输出的参数列表</param>
        /// <returns></returns>
        public static Boolean ExecProcedure_Sql(String SqlStr, Object[] ParameterValues, out DbDataReader DataReader, Boolean IsHaveOutPutPar)
        {
            DataReader = null;
            DataTable iDataTable = new DataTable();
            return ExecProcedure_Sql(SqlStr, ParameterValues, IsHaveOutPutPar, true, false, ref iDataTable, out DataReader);
        }

        /// <summary>
        /// 执行分页查询的存储过程
        /// </summary>
        /// <param name="TabelName">表名</param>
        /// <param name="Cols">需要查询的列</param>
        /// <param name="MaxRows">最大行数</param>
        /// <param name="OnePageRows">一页的行数</param>
        /// <param name="StartRowIndex">开始的行数</param>
        /// <param name="SeleStr">查询条件</param>
        /// <param name="KeyCol">主键</param>
        /// <param name="OrderCols">排序列</param>
        /// <param name="DataReader">返回的DbDataReader对象</param>
        /// <returns></returns>
        public static Boolean ExecProcedure_pagehelper(String TabelName,String Cols,Int32 MaxRows,Int32 OnePageRows
            ,Int32 StartRowIndex,String SeleStr,String KeyCol,String OrderCols,out DbDataReader DataReader)
        {
            DataTable iDBDataTable = new DataTable();
            return ExecProcedure_pagehelper(TabelName, Cols, MaxRows, OnePageRows, StartRowIndex, SeleStr, KeyCol, OrderCols
                , true, out DataReader, ref iDBDataTable);
        }

        /// <summary>
        /// 执行分页查询的存储过程
        /// </summary>
        /// <param name="TabelName">表名</param>
        /// <param name="Cols">需要查询的列</param>
        /// <param name="MaxRows">最大行数</param>
        /// <param name="OnePageRows">一页的行数</param>
        /// <param name="StartRowIndex">开始的行数</param>
        /// <param name="SeleStr">查询条件</param>
        /// <param name="KeyCol">主键</param>
        /// <param name="OrderCols">排序列</param>
        /// <param name="DataReader">返回的DataTable对象</param>
        /// <returns></returns>
        public static Boolean ExecProcedure_pagehelper(String TabelName, String Cols, Int32 MaxRows, Int32 OnePageRows
            , Int32 StartRowIndex, String SeleStr, String KeyCol, String OrderCols, ref DataTable DbDataTable)
        {
            DbDataReader iDBDataReader = null;
            return ExecProcedure_pagehelper(TabelName, Cols, MaxRows, OnePageRows, StartRowIndex, SeleStr, KeyCol, OrderCols
                , false, out iDBDataReader, ref DbDataTable);
        }

        /// <summary>
        /// 执行分页查询的存储过程
        /// </summary>
        /// <param name="TabelName">表名</param>
        /// <param name="Cols">需要查询的列</param>
        /// <param name="MaxRows">最大行数</param>
        /// <param name="OnePageRows">一页的行数</param>
        /// <param name="StartRowIndex">开始的行数</param>
        /// <param name="SeleStr">查询条件</param>
        /// <param name="KeyCol">主键</param>
        /// <param name="OrderCols">排序列</param>
        /// <param name="ReaderOrTable">返回数据读取器还是数据表(true为数据读取器，false为数据表)</param>
        /// <param name="DataReader">返回的DbDataReader对象</param>
        /// <param name="DBDataTable">返回的DataTable对象</param>
        /// <returns></returns>
        public static Boolean ExecProcedure_pagehelper(String TabelName, String Cols, Int32 MaxRows, Int32 OnePageRows
            , Int32 StartRowIndex, String SeleStr, String KeyCol, String OrderCols
            ,Boolean ReaderOrTable, out DbDataReader DataReader,ref DataTable DBDataTable)
        {
            return ExecProcedure_pagehelper(TabelName, Cols, MaxRows, OnePageRows, StartRowIndex, SeleStr, KeyCol, OrderCols
    , ReaderOrTable, out DataReader, ref DBDataTable, aDefConnConfName);
        }

        /// <summary>
        /// 执行分页查询的存储过程
        /// </summary>
        /// <param name="TabelName">表名</param>
        /// <param name="Cols">需要查询的列</param>
        /// <param name="MaxRows">最大行数</param>
        /// <param name="OnePageRows">一页的行数</param>
        /// <param name="StartRowIndex">开始的行数</param>
        /// <param name="SeleStr">查询条件</param>
        /// <param name="KeyCol">主键</param>
        /// <param name="OrderCols">排序列</param>
        /// <param name="ReaderOrTable">返回数据读取器还是数据表(true为数据读取器，false为数据表)</param>
        /// <param name="DataReader">返回的DbDataReader对象</param>
        /// <param name="DBDataTable">返回的DataTable对象</param>
        /// <param name="Key">配置名称</param>
        /// <returns></returns>
        public static Boolean ExecProcedure_pagehelper(String TabelName, String Cols, Int32 MaxRows, Int32 OnePageRows
            , Int32 StartRowIndex, String SeleStr, String KeyCol, String OrderCols
            , Boolean ReaderOrTable, out DbDataReader DataReader, ref DataTable DBDataTable,String Key)
        {
            DataReader = null;

            Boolean iSqlRet = false;
            Object[] iParameter = new Object[8];
            iParameter[0] = TabelName;
            iParameter[1] = Cols;
            iParameter[2] = MaxRows;
            iParameter[3] = OnePageRows;
            iParameter[4] = StartRowIndex;
            iParameter[5] = SeleStr;
            iParameter[6] = KeyCol;
            iParameter[7] = OrderCols;

            if (ReaderOrTable)
            {
                iSqlRet = ExecProcedure_Sql("pagehelper", iParameter, out DataReader, Key);
                if (!iSqlRet)
                {
                    if (DataReader != null)
                    {
                        DataReader.Close();
                        DataReader.Dispose();
                        DataReader = null;
                    }

                    return false;
                }

                if (DataReader == null)
                {
                    return false;
                }
            }
            else
            {
                iSqlRet = ExecProcedure_Sql("pagehelper", iParameter, ref DBDataTable, Key);
                if (!iSqlRet)
                {
                    return false;
                }
            }

            return true;
        }

        /// <summary>
        /// 执行分页查询的存储过程
        /// </summary>
        /// <param name="TabelName">表名</param>
        /// <param name="MaxRows">最大行数</param>
        /// <param name="SeleStr">查询条件</param>
        /// <param name="KeyCol">主键</param>
        /// <param name="OnePageRows">一页的行数</param>
        /// <param name="PageCount">返回的总页数</param>
        /// <returns></returns>
        public static Boolean ExecProcedure_pagehelper_Count(String TabelName, Int32 MaxRows
            , String SeleStr, String KeyCol, Int32 OnePageRows, out Int32 PageCount)
        {
            return ExecProcedure_pagehelper_Count(TabelName, MaxRows, SeleStr, KeyCol, OnePageRows, out PageCount, aDefConnConfName);
        }

        /// <summary>
        /// 执行分页查询的存储过程
        /// </summary>
        /// <param name="TabelName">表名</param>
        /// <param name="MaxRows">最大行数</param>
        /// <param name="SeleStr">查询条件</param>
        /// <param name="KeyCol">主键</param>
        /// <param name="OnePageRows">一页的行数</param>
        /// <param name="PageCount">返回的总页数</param>
        /// <param name="Key">配置名称</param>
        /// <returns></returns>
        public static Boolean ExecProcedure_pagehelper_Count(String TabelName, Int32 MaxRows
            , String SeleStr, String KeyCol, Int32 OnePageRows, out Int32 PageCount,String Key)
        {
            PageCount = 0;

            Boolean iSqlRet = false;
            Object[] iParameter_Count = new Object[5];
            iParameter_Count[0] = TabelName;
            iParameter_Count[1] = MaxRows;
            iParameter_Count[2] = SeleStr;
            iParameter_Count[3] = KeyCol;
            iParameter_Count[4] = 0;

            iSqlRet = ExecProcedure_Sql("pagehelper_Count", iParameter_Count, true, Key);
            if (!iSqlRet)
            {
                PageCount = 0;
                return false;
            }

            if (iParameter_Count[4] == null)
            {
                PageCount = 0;
                return false;
            }

            Int32 iDataCount = 0;
            if (!(Int32.TryParse(iParameter_Count[4].ToString(), out iDataCount)))
            {
                PageCount = 0;
                return false;
            }
            else
            {
                PageCount = iDataCount / OnePageRows;
                if ((iDataCount % OnePageRows) > 0)
                {
                    PageCount++;
                }
            }

            return true;
        }

        #endregion

        #region 私有方法

        private static Boolean GetParametersForCommStr_MsSql(SqlCommand CommObj)
        {
            if (CommObj == null)
            {
                return false;
            }
            try
            {
                System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(CommObj);
                CommObj.Parameters.RemoveAt(0);
            }
            catch(Exception Err)
            {
                aErrMsg = Err.Message;
                CommObj.Parameters.Clear();
                return false;
            }

            return true;
        }

        //private static Boolean GetParametersForCommStr_MySql(MySqlCommand CommObj)
        //{
        //    if (CommObj == null)
        //    {
        //        return false;
        //    }
        //    try
        //    {
        //        MySqlCommandBuilder.DeriveParameters(CommObj);
        //        //CommObj.Parameters.RemoveAt(0);
        //    }
        //    catch (Exception Err)
        //    {
        //        aErrMsg = Err.Message;
        //        CommObj.Parameters.Clear();
        //        return false;
        //    }

        //    return true;
        //}

        #endregion
    }
}
